package org.tarena.dang.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.tarena.dang.dao.BookDAO;
import org.tarena.dang.entity.Book;
import org.tarena.dang.entity.Product;
import org.tarena.dang.util.DbUtil;

public class BookDAOImpl implements BookDAO {
	//private static final String findByParentIdAll= "select * from(select d.* from (select * from d_category_product  where cat_id = ?) s join d_product d on(d.id=s.product_id) where s.product_id = d.id) f join d_book b on(b.id=f.id) where f.id=b.id";
	private static final String findByCid = "select * from(select d.* from (select * from d_category_product  where cat_id = ?) s join d_product d on(d.id=s.product_id) where s.product_id = d.id) f join d_book b on(b.id=f.id) where f.id=b.id limit ?, ?";
	private static final String findByCiddAll = "select count(*) as nn from (select d.* from (select * from d_category_product  where cat_id = ?) s join d_product d on(d.id=s.product_id) where s.product_id = d.id) f join d_book b on(b.id=f.id) where f.id=b.id ";
	private static final String findRecomBook = "select * from d_product dp join d_book db on(dp.id=db.id) limit ?,? ";
	//private static final String findByPriceD = "select * from(select d.* from (select * from d_category_product  where cat_id = ?) s join d_product d on(d.id=s.product_id) where s.product_id = d.id) f join d_book b on(b.id=f.id) where f.id=b.id order by dang_price limit ?,?";	
	private static final String findByTimeD = "select * from(select d.* from (select * from d_category_product  where cat_id = ?) s join d_product d on(d.id=s.product_id) where s.product_id = d.id) f join d_book b on(b.id=f.id) where f.id=b.id order by add_time limit ?,?";	
	private static final String findByPriceD = "select * from v_product where cat_id=? order by dang_price limit ?,?";

	@Override
	public List<Book> findByCid(int cid,int currentPage,int pageSize) throws SQLException {
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(findByCid);
		pst.setInt(1, cid);
		pst.setInt(2, pageSize*(currentPage-1));
		pst.setInt(3, pageSize);
		ResultSet re = pst.executeQuery();
		List<Book> list = new ArrayList<Book>();
		while(re.next()){
			Book book = new Book();
			book.setId(re.getInt("id"));
			book.setDescription(re.getString("description"));
			book.setProductName(re.getString("product_name"));
			book.setAddTime(re.getLong("add_time"));
			book.setFixedPrice(re.getDouble("fixed_price"));
			book.setDangPrice(re.getDouble("dang_price"));
			book.setKeyWords(re.getString("keywords"));
			book.setProductPic(re.getString("product_pic"));
			book.setAuthor(re.getString("author"));
			book.setPublishing(re.getString("publishing"));
			book.setPublishTime(re.getLong("publish_time"));
			list.add(book);
		}
		DbUtil.closeConnection();
		return list;
	}

	@Override
	public int findByCiddAll(int cid) throws SQLException {
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(findByCiddAll);
		pst.setInt(1, cid);
		ResultSet re = pst.executeQuery();
		re.next();
		return re.getInt(1);
	}

	@Override
	public List<Book> findRecomBook(int n, int recomBookNnm) throws SQLException {
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(findRecomBook);
		pst.setInt(1, n);
		pst.setInt(2, recomBookNnm);
		ResultSet re = pst.executeQuery();
		List<Book> list = new ArrayList<Book>();
		while(re.next()){
			Book book = new Book();
			book.setId(re.getInt("id"));
			book.setProductName(re.getString("product_name"));
			book.setAuthor(re.getString("author"));
			book.setPublish(re.getString("publishing"));
			book.setPublishTime(re.getLong("publish_time"));
			book.setDescription(re.getString("description"));
			book.setFixedPrice(re.getDouble("fixed_price"));
			book.setDangPrice(re.getDouble("dang_price"));
			book.setProductPic(re.getString("product_pic"));
			list.add(book);
		}
		return list;
	}

	@Override
	public List<Book> findByPriceD(int cid, int currentPage, int pageSize)
			throws SQLException {
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(findByPriceD);
		pst.setInt(1, cid);
		pst.setInt(2, pageSize*(currentPage-1));
		pst.setInt(3, pageSize);
		ResultSet re = pst.executeQuery();
		List<Book> list = new ArrayList<Book>();
		while(re.next()){
			Book book = new Book();
			book.setId(re.getInt("id"));
			book.setDescription(re.getString("description"));
			book.setProductName(re.getString("product_name"));
			book.setAddTime(re.getLong("add_time"));
			book.setFixedPrice(re.getDouble("fixed_price"));
			book.setDangPrice(re.getDouble("dang_price"));
			book.setKeyWords(re.getString("keywords"));
			book.setProductPic(re.getString("product_pic"));
			book.setAuthor(re.getString("author"));
			book.setPublishing(re.getString("publishing"));
			book.setPublishTime(re.getLong("publish_time"));
			list.add(book);
		}
		DbUtil.closeConnection();
		System.out.println("按价格排序执行中...");
		return list;
	}

	@Override
	public List<Book> findBySort(int cid, int currentPage,int pageSize,String orderBy)
			throws SQLException {
		String sql = "select * from v_product where cat_id=?  "+orderBy+"  limit ?,?";
		System.out.println(sql);
		System.out.println(cid+"*****"+currentPage+"*****"+pageSize);
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(sql);
		pst.setInt(1, cid);
		pst.setInt(2, pageSize*(currentPage-1));
		pst.setInt(3, pageSize);
		ResultSet re = pst.executeQuery();
		List<Book> list = new ArrayList<Book>();
		while(re.next()){
			Book book = new Book();
			book.setId(re.getInt("product_id"));
			book.setDescription(re.getString("description"));
			book.setProductName(re.getString("product_name"));
			book.setAddTime(re.getLong("add_time"));
			book.setFixedPrice(re.getDouble("fixed_price"));
			book.setDangPrice(re.getDouble("dang_price"));
			book.setKeyWords(re.getString("keywords"));
			book.setProductPic(re.getString("product_pic"));
			book.setAuthor(re.getString("author"));
			book.setPublishing(re.getString("publishing"));
			book.setPublishTime(re.getLong("publish_time"));
			list.add(book);
		}
		DbUtil.closeConnection();
		System.out.println(orderBy+"执行中...");
		return list;
	}

	@Override
	public List<Book> findBySortAll(int parentId, int currentPage,
			int pageSize, String orderBy) throws SQLException {
		String sql = "select * from v_category where parent_id=?  "+orderBy+"  limit ?,?";
		System.out.println(sql);
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(sql);
		pst.setInt(1, parentId);
		pst.setInt(2, pageSize*(currentPage-1));
		pst.setInt(3, pageSize);
		ResultSet re = pst.executeQuery();
		List<Book> list = new ArrayList<Book>();
		while(re.next()){
			Book book = new Book();
			book.setId(re.getInt("product_id"));
			book.setDescription(re.getString("description"));
			book.setProductName(re.getString("product_name"));
			book.setAddTime(re.getLong("add_time"));
			book.setFixedPrice(re.getDouble("fixed_price"));
			book.setDangPrice(re.getDouble("dang_price"));
			book.setKeyWords(re.getString("keywords"));
			book.setProductPic(re.getString("product_pic"));
			book.setAuthor(re.getString("author"));
			book.setPublishing(re.getString("publishing"));
			book.setPublishTime(re.getLong("publish_time"));
			list.add(book);
		}
		DbUtil.closeConnection();
		System.out.println(orderBy+"执行中...");
		return list;
	}

	@Override
	public int findByPageSizeAll(int parentId) throws SQLException {
		String sql = "select count(*) from v_category where parent_id=?";
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(sql);
		pst.setInt(1, parentId);
		ResultSet re = pst.executeQuery();
		re.next();
		return re.getInt(1);
		
	}

	@Override
	public List<Book> searchByKeyWords(String keyWords) throws SQLException {
		String sql = "select * from v_product where product_name like '%"+keyWords+"%' or author like '%"+keyWords+"%' ";
		System.out.println(sql +keyWords);
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(sql);
		ResultSet re = pst.executeQuery();
		List<Book> list = new ArrayList<Book>();
		while(re.next()){
			Book book = new Book();
			book.setId(re.getInt("product_id"));
			book.setDescription(re.getString("description"));
			book.setProductName(re.getString("product_name"));
			book.setAddTime(re.getLong("add_time"));
			book.setFixedPrice(re.getDouble("fixed_price"));
			book.setDangPrice(re.getDouble("dang_price"));
			book.setKeyWords(re.getString("keywords"));
			book.setProductPic(re.getString("product_pic"));
			book.setAuthor(re.getString("author"));
			book.setPublishing(re.getString("publishing"));
			book.setPublishTime(re.getLong("publish_time"));
			list.add(book);
		}
		DbUtil.closeConnection();
		System.out.println("搜索 --"+keyWords+"-- 执行中...ok");
		System.out.println(sql);
		return list;
	}

	@Override
	public List<Book> searchByKeyWords(String keyWords, int cid)
			throws SQLException {
		String sql = "select * from v_product where (product_name like '%"+keyWords+"%' or author like '%"+keyWords+"%') and cat_id=?";
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(sql);
		pst.setInt(1, cid);
		ResultSet re = pst.executeQuery();
		List<Book> list = new ArrayList<Book>();
		while(re.next()){
			Book book = new Book();
			book.setId(re.getInt("product_id"));
			book.setDescription(re.getString("description"));
			book.setProductName(re.getString("product_name"));
			book.setAddTime(re.getLong("add_time"));
			book.setFixedPrice(re.getDouble("fixed_price"));
			book.setDangPrice(re.getDouble("dang_price"));
			book.setKeyWords(re.getString("keywords"));
			book.setProductPic(re.getString("product_pic"));
			book.setAuthor(re.getString("author"));
			book.setPublishing(re.getString("publishing"));
			book.setPublishTime(re.getLong("publish_time"));
			list.add(book);
		}
		DbUtil.closeConnection();
		System.out.println("搜索 --"+keyWords+"-- 执行中...");
		System.out.println(sql);
		return list;
		
	}

	@Override
	public Product findByPid(int pid) throws SQLException {
		String sql = "select * from d_product where id = ?";
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(sql);
		pst.setInt(1, pid);
		ResultSet re = pst.executeQuery();
		Product book = new Book();
		while(re.next()){
			book.setId(re.getInt("id"));
			book.setProductName(re.getString("product_name"));
			book.setDangPrice(re.getDouble("dang_price"));
			book.setFixedPrice(re.getDouble("fixed_price"));
			book.setProductPic(re.getString("product_pic"));
			
		}
		DbUtil.closeConnection();
		return book;
	}

	@Override
	public Book findByProId(int id) throws SQLException {
		String sql = "select * from v_product where product_id=?";
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(sql);
		pst.setInt(1, id);
		ResultSet re = pst.executeQuery();
		Book book = new Book();
		while(re.next()){
			book.setId(re.getInt("product_id"));
			//book.setDescription(re.getString("description"));
			book.setProductName(re.getString("product_name"));
			book.setAddTime(re.getLong("add_time"));
			book.setFixedPrice(re.getDouble("fixed_price"));
			book.setDangPrice(re.getDouble("dang_price"));
			book.setKeyWords(re.getString("keywords"));
			book.setProductPic(re.getString("product_pic"));
			book.setAuthor(re.getString("author"));
			book.setPublishing(re.getString("publishing"));
			book.setPublishTime(re.getLong("publish_time"));
		
		}
		DbUtil.closeConnection();
		System.out.println(sql);
		return book;
	}

	@Override
	public List<Book> findHotBooks() throws SQLException {
		String sql = "select * from v_product order by product_num desc limit 8";
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(sql);
		ResultSet re = pst.executeQuery();
		List<Book> list = new ArrayList<Book>();
		while(re.next()){
			Book book = new Book();
			book.setId(re.getInt("product_id"));
			book.setDescription(re.getString("description"));
			book.setProductName(re.getString("product_name"));
			book.setAddTime(re.getLong("add_time"));
			book.setFixedPrice(re.getDouble("fixed_price"));
			book.setDangPrice(re.getDouble("dang_price"));
			book.setKeyWords(re.getString("keywords"));
			book.setProductPic(re.getString("product_pic"));
			book.setAuthor(re.getString("author"));
			book.setPublishing(re.getString("publishing"));
			book.setPublishTime(re.getLong("publish_time"));
			book.setPnum(re.getInt("product_num"));
			list.add(book);
		}
		DbUtil.closeConnection();
		System.out.println("hotbook==="+sql);
		return list;
	}


}
